-- 2012-05-17 EM
use dbproc
go
alter procedure spx_get_deposits
@OwnerAcc_id int,
@res varchar(max) output
as
begin

exec sms_p.dbo.Prints 'XGD 000','spx_get_deposits:', @OwnerAcc_id,32

declare @rows int;

declare @tbl table(O_id int, list varchar(2048), summ money);

insert into @tbl
	SELECT a1.OwnerAcc_id,
       ( SELECT cast(id as varchar) + ','

           FROM dbdata.dbo.accounts as  a2

          WHERE a2.OwnerAcc_id = a1.OwnerAcc_id 

          ORDER BY id

            FOR XML PATH('') ) AS List, 0

    FROM dbdata.dbo.accounts as a1
    			join dbdata.dbo.account_categs as ac on a1.categ_id = ac.id 
			join dbdata.dbo.users as u on u.id=a1.uid
			where 
		 u.dt_locked is null and code='6' and a1.enabled=1
    and a1.OwnerAcc_id is not null and a1.OwnerAcc_id >= @OwnerAcc_id
    GROUP BY OwnerAcc_id ;
      
      
update @tbl set list=','+list

select * from @tbl

declare @O_id int, @list varchar(2048), @summ money;

DECLARE c CURSOR FOR
        SELECT O_Id, List
          FROM @tbl
         ORDER BY O_Id;

        OPEN c ;

        FETCH NEXT FROM c INTO @O_id, @list ;

        WHILE @@FETCH_STATUS = 0 
			BEGIN
				update @tbl set list=REPLACE(list,','+CAST(@o_id as varchar)+',',','+CAST(@o_id as varchar)+@list);


        FETCH NEXT FROM c INTO @O_id, @list;

        END;


        CLOSE c ;

update @tbl set list=','+CAST(o_id as varchar)+list 
select * from @tbl


        OPEN c ;

        FETCH NEXT FROM c INTO @O_id, @list ;

        WHILE @@FETCH_STATUS = 0 
			BEGIN
exec sms_p.dbo.Prints 'XGD 075','list:', @List,32

				select @summ=sum(summary) 
					from dbdata.dbo.accounts as a
					  join dbdata.dbo.account_categs as ac on a.categ_id = ac.id 
						join dbdata.dbo.users as u on u.id=a.uid
				where 
				u.dt_locked is null and code='6' and a.enabled=1
					and  @list like  '%,'+cast(a.id as varchar)+',%' 

exec sms_p.dbo.Prints 'XGD 085','summ:', @summ,32

				update @tbl set summ=@summ where O_id=@O_id 
            


        FETCH NEXT FROM c INTO @O_id, @list;

        END;


        CLOSE c ;


DEALLOCATE c ;


select * from @tbl

insert into @tbl
	SELECT a.id, a.id, summary      
    FROM dbdata.dbo.accounts as a
    			join dbdata.dbo.account_categs as ac on a.categ_id = ac.id 
			join dbdata.dbo.users as u on u.id=a.uid
			where 
		 u.dt_locked is null and code='6' and a.enabled=1
    and a.OwnerAcc_id is not null and a.OwnerAcc_id >= @OwnerAcc_id
    and a.id not in (select o_id from @tbl)
    
    
select * from @tbl






declare @xmlRes xml
	set @xmlRes = (
--	select a.id, sms_p.dbo.udf_displaysum2(t.summ) as summary, isnull(m.FirstName,'')+isnull(m.LastName,'') as name, p.phone 
	select a.id, t.summ as summary, isnull(m.FirstName,'')+isnull(m.LastName,'') as name, p.phone, uc.title as title
	from dbdata.dbo.accounts as a
			join dbdata.dbo.account_categs as ac on categ_id = ac.id 
			join dbdata.dbo.users as u on u.id=a.uid
			join dbdata.dbo.phones as p on p.uid=u.id
			join Management.dbo.Users as m on u.name=m.ID
			join @tbl as t on t.o_id=a.id
			join dbdata.dbo.user_categs as uc on uc.id=u.categ_id
			where 
			p.as_default=1 and p.dt_removed is null and p.enabled=1
			and u.dt_locked is null and
			ac.code='6' and a.enabled=1 and a.OwnerAcc_id=@OwnerAcc_id
			order by a.id for xml raw('deposit'), root('DepositList'), type
	);
	set @res=CONVERT(varchar(max),@xmlRes)
end;
go

grant execute on spx_get_deposits to Executer;

/*
DECLARE	
		@res varchar(max)

EXEC	dbproc.[dbo].[spx_get_deposits]
		@OwnerAcc_id = 5,
		@res = @res OUTPUT

SELECT	@res as N'@res'
select Cast(@res as xml)

*/
